package com.etone.smartAudit.utils.database;

import com.etone.smartAudit.domain.DataMeta;
import com.etone.smartAudit.enums.DataBaseType;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author: sovy
 * @date: 2020/11/20
 * @description:
 */
public class MysqlUtil {
    /**
     * 创建表
     *
     * @param ip
     * @param userName
     * @param password
     * @param port
     * @param database
     * @param tableName
     * @param dataMetas
     */
    public static void createTable(String ip, String userName, String password, Integer port, String database, String tableName, List<DataMeta> dataMetas) {
        String createTableSql = DatabaseUtil.splitSpace("CREATE", "TABLE", tableName, "(");
        List<String> sbs = new ArrayList<>();
        for (DataMeta dataMeta : dataMetas) {
            String columnSql = dataMeta.getFieldName();
            sbs.add(columnSql);
        }
        //字段
        String fields = dataMetas.stream().map(dataMeta -> {
            String columnSql = DatabaseUtil.splitSpace("`" + dataMeta.getFieldName() + "`", dataMeta.getType());
            if (dataMeta.getNullable() != null && !dataMeta.getNullable()) {
                columnSql += DatabaseUtil.splitSpace("NOT", "NULL");
            }
            if (dataMeta.getDefaultValue() != null) {
                columnSql += DatabaseUtil.splitSpace("DEFAULT", dataMeta.getDefaultValue());
            }
            if (dataMeta.getComment() != null) {
                columnSql += DatabaseUtil.splitSpace("COMMENT", "'", dataMeta.getComment(), "'");
            }
            return columnSql;
        }).collect(Collectors.joining(","));

        //主键
        String primaryKeys = dataMetas.stream().filter(dataMeta -> dataMeta.getPrimaryKey() != null && dataMeta.getPrimaryKey()).map(dataMeta -> dataMeta.getFieldName()).collect(Collectors.joining(","));
        if (!StringUtils.isEmpty(primaryKeys)) {
            primaryKeys = DatabaseUtil.splitSpace(",", "PRIMARY", "KEY", "(", primaryKeys, ")");
        } else {
            primaryKeys = "";
        }

        //唯一约束
        String uniqueKeys = dataMetas.stream().filter(dataMeta -> dataMeta.getUniqueKey() != null && dataMeta.getUniqueKey()).map(dataMeta -> DatabaseUtil.splitSpace("UNIQUE", "INDEX", "(", dataMeta.getFieldName(), ")")).collect(Collectors.joining(","));
        if (!StringUtils.isEmpty(uniqueKeys)) {
            uniqueKeys = DatabaseUtil.splitSpace(",", uniqueKeys);
        } else {
            uniqueKeys = "";
        }
        createTableSql = DatabaseUtil.splitSpace(createTableSql, fields, primaryKeys, uniqueKeys, ")");


        DatabaseUtil.execSql(ip, userName, password, port, database, Arrays.asList(createTableSql), DataBaseType.MYSQL);
    }

    /**
     * 表授权
     *
     * @param ip
     * @param userName
     * @param password
     * @param port
     * @param database
     * @param tableName
     * @param toUserName
     */
    public static void authTableSelect(String ip, String userName, String password, Integer port, String database, String tableName, String toUserName) {

        String authSql = "GRANT Select ON TABLE `" + database + "`.`" + tableName + "` TO `" + toUserName + "`@`%`";

        DatabaseUtil.execSql(ip, userName, password, port, database, Arrays.asList(authSql), DataBaseType.MYSQL);
    }

    /**
     * 删除表
     *
     * @param ip
     * @param userName
     * @param password
     * @param port
     * @param database
     * @param tableName
     */
    public static void deleteTable(String ip, String userName, String password, Integer port, String database, String tableName) {
        String deleteSql = "drop table if exists " + tableName;
        DatabaseUtil.execSql(ip, userName, password, port, database, Arrays.asList(deleteSql), DataBaseType.MYSQL);
    }
}
